 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DAL
{
    public static class Selector
    {
        #region Get Pilots
        public static List<Pilot> GetPilots()
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Pilot> tPilot = from cs in ctx.Pilot
                         select cs;

                return tPilot.ToList();
            }
           
        }
        public static List<Pilot> GetPilotsByName(string mSname)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Pilot> tPilot = from cs in ctx.Pilot
                                           where cs.sname == mSname
                                           select cs;
                return tPilot.ToList();
            }
        }
        public static List<Pilot> GetPilotsByName(string mSname, string mName)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Pilot> tPilot = from cs in ctx.Pilot
                                           where cs.sname == mSname && cs.name == mName
                                           select cs;
                return tPilot.ToList();
            }
        }
        public static List<Pilot> GetPilotsByCompnay(string mCompany)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Pilot> tPilot = from cs in ctx.Pilot
                                           where cs.company == mCompany
                                           select cs;
                return tPilot.ToList();
            }
        }
        public static List<Pilot> GetPilotsByQvalification(string mQvalification)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Pilot> tPilot = from cs in ctx.Pilot
                                           where cs.qualification == mQvalification
                                           select cs;
                return tPilot.ToList();
            }
        }
        public static List<Pilot> GetPilotsByFlyTime(int mFlyTime)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Pilot> tPilot = from cs in ctx.Pilot
                                           where cs.ftime == mFlyTime
                                           select cs;
                return tPilot.ToList();
            }
        }
        public static List<Pilot> GetPilotsByBDate(DateTime mBDate)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Pilot> tPilot = from cs in ctx.Pilot
                                           where cs.bdate == mBDate
                                           select cs;
                return tPilot.ToList();
            }
        }
        #endregion

        #region Get Instructors

        public static List<Instructor> GetInstructors()
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Instructor> tInstructor = from cs in ctx.Instructor
                                           select cs;

                return tInstructor.ToList();
            }

        }

        public static List<Instructor> GetInstructorsByName(string mSname)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Instructor> tInstructor = from cs in ctx.Instructor
                                                     where cs.sname == mSname
                                                     select cs;

                return tInstructor.ToList();
            }

        }

        public static List<Instructor> GetInstructorsByName(string mSname, string mName)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Instructor> tInstructor = from cs in ctx.Instructor
                                                     where cs.sname == mSname && cs.name == mName
                                                     select cs;
                return tInstructor.ToList();
            }
        }

        public static List<Instructor> GetInstructorsByBDate(DateTime mBDate)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Instructor> tInstructor = from cs in ctx.Instructor
                                                     where cs.bdate == mBDate
                                                     select cs;
                return tInstructor.ToList();
            }

        }

        #endregion

        #region Get Planes

        public static List<Plane> GetPlanes()
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Plane> tPlane = from cs in ctx.Plane
                                           
                                           select cs;
                return tPlane.ToList();
            }
        }

        public static List<Plane> GetPlanesByName(string mName)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Plane> tPlane = from cs in ctx.Plane
                                           where cs.name == mName
                                           select cs;
                return tPlane.ToList();
            }
        }

        public static List<Plane> GetPlanesInfo(string mName)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Plane> tPlane = from cs in ctx.Plane
                                           where cs.name == mName
                                           select cs;
                return tPlane.ToList();
            }
        }

        public static List<Plane> GetPlanesInfo(string mName, int id)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Plane> tPlane = from cs in ctx.Plane
                                           where cs.name == mName && cs.id_plane == id
                                           select cs;
                return tPlane.ToList();
            }
        }
        

        #endregion

        #region Get Flight

        public static List<Flight> GetFlights()
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Flight> tFlight = from cs in ctx.Flight
                                           select cs;
                return tFlight.ToList();
            }
        }

        public static List<Flight> GetFlights(Plane mPlane, Instructor mInstrucotr, int mNumber, DateTime mDate)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Flight> tFlight = from cs in ctx.Flight
                                             where cs.fk_plane == mPlane.id_plane && cs.fk_instr == mInstrucotr.id_instr && cs.number == mNumber && cs.date == mDate
                                             select cs;
                return tFlight.ToList();
            }
        }

        public static List<Flight> GetFlightsID(int mNumber)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Flight> tFlight = from cs in ctx.Flight
                                             where cs.number == mNumber
                                             select cs;
                return tFlight.ToList();
            }
        }

        public static List<Flight> GetFlightsByDate(int mNumber)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Flight> tFlight = from cs in ctx.Flight
                                             where cs.number == mNumber
                                             select cs;
                return tFlight.ToList();
            }
        }

        public static List<Flight> GetFlightsByDate(int mNumber, int mId)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Flight> tFlight = from cs in ctx.Flight
                                             where cs.number == mNumber && cs.id_flight == mId
                                             select cs;
                return tFlight.ToList();
            }
        }


        public static List<Flight> GetFlightsByDate(DateTime mDate)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Flight> tFlight = from cs in ctx.Flight
                                             where cs.date == mDate
                                             select cs;
                return tFlight.ToList();
            }
        }

        public static List<Flight> GetFlightsByDate(DateTime mStartDate, DateTime  mEndDate)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Flight> tFlight = from cs in ctx.Flight
                                             where cs.date >= mStartDate && cs.date <= mEndDate
                                             select cs;
                return tFlight.ToList();
            }
        }



        public static List<Flight> GetFlightsByTime(TimeSpan mStartTime)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Flight> tFlight = from cs in ctx.Flight
                                             where cs.begine_time == mStartTime 
                                             select cs;
                return tFlight.ToList();
            }
        }

        public static List<Flight> GetFlightsByTime(TimeSpan mEndTime , int alwaysZero)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Flight> tFlight = from cs in ctx.Flight
                                             where cs.end_time == mEndTime
                                             select cs;
                return tFlight.ToList();
            }
        }

        public static List<Flight> GetFlightsByTime(TimeSpan mStartTime, TimeSpan mEndTime)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Flight> tFlight = from cs in ctx.Flight
                                             where cs.end_time <= mEndTime && cs.begine_time >= mStartTime
                                             select cs;
                return tFlight.ToList();
            }
        }

        public static List<Flight> GetFlightsByPlane(Plane mPlane)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Flight> tFlight = from cs in ctx.Flight
                                             where cs.fk_plane == mPlane.id_plane
                                             select cs;
                return tFlight.ToList();
            }
        }

        public static List<Flight> GetFlightsByInstructor(Instructor mInstructor)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Flight> tFlight = from cs in ctx.Flight
                                             where cs.fk_instr == mInstructor.id_instr
                                             select cs;
                return tFlight.ToList();
            }
        }

        public static List<Flight> GetFlightsByCondition(Condition mCondition)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Flight> tFlight = from cs in ctx.Flight
                                             where cs.fk_condition == mCondition.id_condition
                                             select cs;
                return tFlight.ToList();
            }
        }


        #endregion

        #region Get Crew

        public static List<Crew> GetCrew()
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Crew> tCrew = from cs in ctx.Crew
                                             select cs;
                return tCrew.ToList();
            }
        }

        public static List<Crew> GetCrew(Pilot mPilot, Flight mFlight, string mRole)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Crew> tCrew = from cs in ctx.Crew
                                         where cs.fk_pilot == mPilot.id_pilot && cs.fk_flight == mFlight.id_flight && cs.role == mRole
                                         select cs;
                return tCrew.ToList();
            }
        }

        public static List<Crew> GetCrew(Pilot mPilot)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Crew> tCrew = from cs in ctx.Crew
                                         where cs.fk_pilot == mPilot.id_pilot
                                         select cs;
                return tCrew.ToList();
            }
        }

        public static List<Crew> GetCrew(Flight mFlight)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Crew> tCrew = from cs in ctx.Crew
                                         where  cs.fk_flight == mFlight.id_flight 
                                         select cs;
                return tCrew.ToList();
            }
        }

        public static List<Crew> GetCrewRole(Pilot mPilot, Flight mFlight)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Crew> tCrew = from cs in ctx.Crew
                                         where cs.fk_pilot == mPilot.id_pilot && cs.fk_flight == mFlight.id_flight 
                                         select cs;
                return tCrew.ToList();
            }
        }

      


        #endregion 


        #region Get Condition

        public static List<Condition> GetConditions()
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Condition> tCrew = from cs in ctx.Condition
                                         select cs;
                return tCrew.ToList();
            }
        }

        public static List<Condition> GetCondition(Condition mCondition)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Condition> tCrew = from cs in ctx.Condition
                                              where cs == mCondition
                                              select cs;
                return tCrew.ToList();
            }
        }

        public static List<Condition> GetConditionByWeather(string mWeather)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Condition> tCrew = from cs in ctx.Condition
                                              where cs.weather == mWeather
                                              select cs;
                return tCrew.ToList();
            }
        }

        public static List<Condition> GetConditionByMap(string mMap)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Condition> tCrew = from cs in ctx.Condition
                                              where cs.map == mMap
                                              select cs;
                return tCrew.ToList();
            }
        }

        public static List<Condition> GetConditionByMap(string mMap)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Condition> tCrew = from cs in ctx.Condition
                                              where cs.map == mMap
                                              select cs;
                return tCrew.ToList();
            }
        }

        public static List<Condition> GetConditionByRoute(string mStartPort , string mDestPort)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Condition> tCrew = from cs in ctx.Condition
                                              where cs.srs_port == mStartPort && cs.dest_port == mDestPort
                                              select cs;
                return tCrew.ToList();
            }
        }

        public static List<Condition> GetConditionByPwind(int mPwind)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Condition> tCrew = from cs in ctx.Condition
                                              where cs.pwind == mPwind 
                                              select cs;
                return tCrew.ToList();
            }
        }

        #endregion

        #region Get Mark

        #endregion

        #region Get ParValue

        public static List<ParValue> GetParValue()
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<ParValue> tParValue = from cs in ctx.ParValue
                                         select cs;
                return tParValue.ToList();
            }
        }

        public static List<ParValue> GetParValueByParametr(Parameters mParams)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<ParValue> tParValue = from cs in ctx.ParValue
                                                 where cs.fk_param == mParams.id_param
                                                 select cs;
                return tParValue.ToList();
            }
        }

        public static List<ParValue> GetParValueByFlight(Flight mFlight)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<ParValue> tParValue = from cs in ctx.ParValue
                                                 where cs.fp_flight == mFlight.id_flight
                                                 select cs;
                return tParValue.ToList();
            }
        }

        public static List<ParValue> GetParValueByWritetime(TimeSpan mTime)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<ParValue> tParValue = from cs in ctx.ParValue
                                                 where cs.wtime == mTime
                                                 select cs;
                return tParValue.ToList();
            }
        }

        public static List<ParValue> GetParValueByID(int mId)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<ParValue> tParValue = from cs in ctx.ParValue
                                                 where cs.id == mId
                                                 select cs;
                return tParValue.ToList();
            }
        }
        #endregion

        #region Get Parameters

        public static List<Parameters> GetParameters()
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Parameters> tParValue = from cs in ctx.Parameters
                                                 select cs;
                return tParValue.ToList();
            }
        }

        public static List<Parameters> GetParameters(Parameters mParam)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Parameters> tParValue = from cs in ctx.Parameters
                                                   where cs == mParam
                                                   select cs;
                return tParValue.ToList();
            }
        }

        public static List<Parameters> GetParametersByName(string mName)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Parameters> tParValue = from cs in ctx.Parameters
                                                   where cs.name == mName
                                                   select cs;
                return tParValue.ToList();
            }
        }

        public static List<Parameters> GetParametersByClass(string mClass)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Parameters> tParValue = from cs in ctx.Parameters
                                                   where cs.@class == mClass
                                                   select cs;
                return tParValue.ToList();
            }
        }

        public static List<Parameters> GetParametersByType(string mType)
        {
            using (testdbsEntities ctx = new testdbsEntities())
            {
                IQueryable<Parameters> tParValue = from cs in ctx.Parameters
                                                   where cs.type == mType
                                                   select cs;
                return tParValue.ToList();
            }
        }

    

        #endregion

    }
}
